package routers

import (
	"encoding/json"
	"fmt"
	"net/http"
	"strconv"
	"time"

	"shqsoft.cn/pas3ui/base"
	"shqsoft.cn/pas3ui/popedom"

	jwt "github.com/dgrijalva/jwt-go"
	"github.com/labstack/echo"
)

// //对公不良认定
func UpCompClieblgmRouter(c echo.Context) error {
	khid := c.FormValue("KHID")
	isbl := c.FormValue("isbl")
	isgm := c.FormValue("isgm")
	tx := base.MYDB.Begin()
	var rd = ""
	rows, err := base.MYDB.Raw(`SELECT "BLDT" from "SH_COMP_BLKH" WHERE "CUID"=?`, khid).Rows()
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	defer rows.Close()
	if rows.Next() {
		rows.Scan(&rd)
	}
	bldt := time.Now()
	gmdt := &bldt
	if isgm == "0" {
		gmdt = nil
	}
	if rd == "" {
		err := tx.Exec(`insert into "SH_COMP_BLKH"("CUID","BLDT","GMDT","ISBL","ISGM") values(?,?,?,?,?)`, bldt, time.Now(), gmdt, isbl, isgm).Error
		if err != nil {
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	} else {
		err := tx.Exec(`UPDATE "SH_COMP_BLKH" SET "ISBL"=?,"ISGM"=? WHERE "CUID"=?`, isbl, isgm, khid).Error
		if err != nil {
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH")
	return c.JSON(http.StatusOK, "OK")
}

func DelClieblgmRouter(c echo.Context) error {
	khid := c.FormValue("KHID")
	tx := base.MYDB.Begin()
	tx.Exec(`DELETE FROM "SH_COMP_BLKH" WHERE "CUID"=?`, khid)
	tx.Exec(`DELETE FROM "SH_COMP_BLKH_DGGL" WHERE "CUID"=?`, khid)
	tx.Exec(`DELETE FROM "SH_COMP_BLKH_DSGL" WHERE "CUID"=?`, khid)
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH")
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH_DGGL")
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH_DSGL")
	ch.Commit()
	return c.JSON(http.StatusOK, "OK")
}

// //对公不良认定--关联对公户
func AddCompbldkClieDghRouter(c echo.Context) error {
	khid := c.FormValue("KHID")
	ID := c.FormValue("ID")
	tx := base.MYDB.Begin()
	var count int
	tx.Raw(`SELECT COUNT(*) FROM "SH_COMP_BLKH_DGGL" WHERE "CUID"=? AND "GLCUID"=?`, khid, ID).Row().Scan(&count)
	if count > 0 {
		return echo.NewHTTPError(http.StatusBadRequest, "该对公客户已关联，请重新添加！")
	}
	tx.Exec(`INSERT INTO "SH_COMP_BLKH_DGGL" VALUES(?,?)`, khid, ID)
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH_DGGL")
	return c.JSON(http.StatusOK, "OK")
}

// //对公不良认定--删除关联对公户
func DelCompbldkClieDghRouter(c echo.Context) error {
	ID := c.FormValue("ID")
	id, _ := strconv.Atoi(ID)
	tx := base.MYDB.Begin()
	tx.Exec(`DELETE FROM "SH_COMP_BLKH_DGGL" WHERE "GLCUID"=?`, id)
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH_DGGL")
	return c.JSON(http.StatusOK, "OK")
}

// //对公不良认定--关联个人户
func AddCompbldkClieGrhRouter(c echo.Context) error {
	khid := c.FormValue("KHID")
	ID := c.FormValue("ID")
	tx := base.MYDB.Begin()
	var count int
	tx.Raw(`SELECT COUNT(*) FROM "SH_COMP_BLKH_DSGL" WHERE "CUID"=? AND "GLCUID"=?`, khid, ID).Row().Scan(&count)
	if count > 0 {
		return echo.NewHTTPError(http.StatusBadRequest, "该个人客户已关联，请重新添加！")
	}
	tx.Exec(`INSERT INTO "SH_COMP_BLKH_DSGL" VALUES(?,?)`, khid, ID)
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH_DSGL")
	return c.JSON(http.StatusOK, "OK")
}

// 对公不良认定--删除关联个人户
func DelCompbldkClieGrhRouter(c echo.Context) error {
	ID := c.FormValue("ID")
	id, _ := strconv.Atoi(ID)
	tx := base.MYDB.Begin()
	tx.Exec(`DELETE FROM "SH_COMP_BLKH_DSGL" WHERE "GLCUID"=?`, id)
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_BLKH_DSGL")
	return c.JSON(http.StatusOK, "OK")
}

// //零售不良认定
func UpIndiClieIsblRouter(c echo.Context) error {
	khid := c.FormValue("KHID")
	rdsj := c.FormValue("RDSJ")
	ISBL := c.FormValue("ISBL")
	tx := base.MYDB.Begin()
	var (
		count int
	)
	rows := base.MYDB.Raw(`SELECT count(*) count from "SH_INDI_BLKH" WHERE "CUID"=?`, khid).Row()
	err := rows.Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		return c.JSON(http.StatusOK, "NO")
	} else {
		tx.Exec(`insert into "SH_INDI_BLKH"("CUID","BLDT","ISBL") values(?,?,?)`, khid, rdsj, ISBL)
		tx.Commit()
		ch := base.CHDB.Begin()
		ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_INDI_BLKH")
		ch.Commit()
		return c.JSON(http.StatusOK, "OK")
	}

}

// //零售不良认定
func UpIndiClieIsbl1Router(c echo.Context) error {
	khid := c.FormValue("KHID")
	tx := base.MYDB.Begin()
	var (
		count int
	)
	rows := base.MYDB.Raw(`SELECT count(*) count from "SH_INDI_BLKH" WHERE "CUID"=?`, khid).Row()
	err := rows.Scan(&count)
	if err != nil {
		return err
	}
	if count < 0 {
		return c.JSON(http.StatusOK, "NO")
	} else {
		tx.Exec(`delete from  "SH_INDI_BLKH" where "CUID"=?`, khid)
		tx.Commit()
		ch := base.CHDB.Begin()
		ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_INDI_BLKH")
		ch.Commit()
		return c.JSON(http.StatusOK, "OK")
	}
}

// 对公存款绩费系数--添加
func AddCompAccoJfxsRouter(c echo.Context) error {
	acidStr := c.FormValue("ACID")
	tzxsStr := c.FormValue("TZXS")
	tzxs2Str := c.FormValue("TZXS2")
	acid, err := strconv.Atoi(acidStr)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数acid不能为空，并且必须为整数！"))
	}
	tzxs, err := strconv.ParseFloat(tzxsStr, 64)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数tzxs不能为空，并且必须为数字！"))
	}
	tzxs2, err := strconv.ParseFloat(tzxs2Str, 64)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数tzxs2不能为空，并且必须为数字！"))
	}
	tx := base.MYDB.Begin()
	var (
		count int
	)
	rows := base.MYDB.Raw(`SELECT count(*) count from "SH_COMP_JFXS" WHERE "ACID"=?`, acid).Row()
	err = rows.Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		err = tx.Exec(`update "SH_COMP_JFXS" set "TZXS"=?,"TZXS2"=? where "ACID"=?`, tzxs, tzxs2, acid).Error
		if err != nil {
			return err
		}
	} else {
		err = tx.Exec(`insert into "SH_COMP_JFXS"("ACID","TZXS","TZXS2") values(?,?,?)`, acid, tzxs, tzxs2).Error
		if err != nil {
			return err
		}
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_JFXS")
	return c.JSON(http.StatusOK, "OK")
}

// 对公存款绩费系数--删除
func DeleteCompAccoJfxsRouter(c echo.Context) error {
	acidStr := c.FormValue("ACID")
	acid, err := strconv.Atoi(acidStr)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数acid不能为空，并且必须为整数！"))
	}
	tx := base.MYDB.Begin()
	err = tx.Exec(`update "SH_COMP_JFXS" set "TZXS"=1,"TZXS2"=1 where "ACID"=?`, acid).Error
	if err != nil {
		return err
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.SH_DICT_COMP_JFXS")
	return c.JSON(http.StatusOK, "OK")
}

// 设置存单质押
func AddCompCdzyRouter(c echo.Context) error {
	acidStr := c.FormValue("ACID")
	acid, err := strconv.Atoi(acidStr)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数acid不能为空，并且必须为整数！"))
	}
	tx := base.MYDB.Begin()
	var count int
	rows := base.MYDB.Raw(`SELECT count(*)  from "SH_INDI_CDZY" WHERE "ACID"=?`, acid).Row()
	err = rows.Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		err = tx.Exec(`update "SH_INDI_CDZY" set "ISCDZY"=1  where "ACID"=?`, acid).Error
		if err != nil {
			return err
		}
	} else {
		err := tx.Exec(`INSERT INTO "SH_INDI_CDZY" ("ACID", "ISCDZY") VALUES (?, 1)`, acid).Error
		if err != nil {
			return err
		}
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.DICT_INDI_CDZY")
	return c.JSON(http.StatusOK, "OK")
}

// 取消存单质押
func DeleteCompCdzyRouter(c echo.Context) error {
	acidStr := c.FormValue("ACID")
	acid, err := strconv.Atoi(acidStr)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数acid不能为空，并且必须为整数！"))
	}
	tx := base.MYDB.Begin()
	var count int
	rows := base.MYDB.Raw(`SELECT count(*)  from "SH_INDI_CDZY" WHERE "ACID"=?`, acid).Row()
	err = rows.Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		err = tx.Exec(`update "SH_INDI_CDZY" set "ISCDZY"=0  where "ACID"=?`, acid).Error
		if err != nil {
			return err
		}
	} else {
		err = tx.Exec(`INSERT INTO "SH_INDI_CDZY" ("ACID", "ISCDZY") VALUES (?, 0)`, acid).Error
		if err != nil {
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.DICT_INDI_CDZY")
	return c.JSON(http.StatusOK, "OK")
}

// 获取KPI打分状态
func GetSjcjzttRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	jd := c.FormValue("JIDU")
	rows := base.MYDB.Raw(`select "STATE" from "SH_JHCW_CONF_STATE" where "NIAN"= ? and "JIDU" = ? `, nf, jd).Row()
	var state int
	rows.Scan(&state)
	res := state == 1
	return c.JSON(http.StatusOK, res)
}

// 设置KPI打分状态
func SetSjcjzttRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	jd := c.FormValue("JIDU")
	ztStr := c.FormValue("ZT")
	zt, err := strconv.ParseInt(ztStr, 10, 8)
	if err != nil {
		return err
	}
	var count uint8
	err = base.MYDB.Raw(`select count(*) from "SH_JHCW_CONF_STATE" where "NIAN"= ? and "JIDU" = ? `, nf, jd).Row().Scan(&count)
	if err != nil {
		return err
	}
	var sqlStr string
	if count > 0 {
		sqlStr = `update "SH_JHCW_CONF_STATE" set "STATE"=? where "NIAN"= ? and "JIDU" = ?`
	} else {
		sqlStr = `insert into "SH_JHCW_CONF_STATE"("STATE","NIAN","JIDU") values(?,?,?)`
	}
	err = base.MYDB.Exec(sqlStr, zt, nf, jd).Error
	if err != nil {
		return err
	}
	return c.JSON(http.StatusOK, "OK")
}

// 获取中后台操作人
func SaveZhtCzrRouter(c echo.Context) (err error) {
	zhtczr := c.FormValue("CZR")
	bm := c.FormValue("BMID")
	err = base.MYDB.Exec(`update "SH_JHCW_ZHT_PERM" set "CZR"=?  where  "BMID" = ? `, zhtczr, bm).Error
	if err != nil {
		return err
	}
	var emid int
	emid, err = strconv.Atoi(zhtczr)
	if err != nil {
		return err
	}
	err = popedom.GrantPermissionToUser(emid, 230514, "K")
	return
}

// 获取中经营单位操作人
func SaveJydwCzrRouter(c echo.Context) (err error) {
	jydwczr := c.FormValue("CZR")
	zb := c.FormValue("ZBID")
	err = base.MYDB.Exec(`update "SH_JHCW_JYDW_PERM" set "CZR"=?  where  "ZBID" = ? `, jydwczr, zb).Error
	if err != nil {
		return err
	}
	var emid int
	emid, err = strconv.Atoi(jydwczr)
	if err != nil {
		return err
	}
	err = popedom.GrantPermissionToUser(emid, 230616, "K")
	return
}

// 获取中后台操作状态
func AddZhtCzztRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	jd := c.FormValue("JIDU")
	var sjmDate string
	if jd == "1" {
		sjmDate = nf + "/03/31"
	} else if jd == "2" {
		sjmDate = nf + "/06/30"
	} else if jd == "3" {
		sjmDate = nf + "/09/30"
	} else {
		sjmDate = nf + "/12/31"
	}
	rows, err := base.MYDB.Raw(`select  distinct "DPID" from "SH_JHCW_HGDF" where "DFJD"=? `, sjmDate).Rows()
	if err != nil {
		return err
	}
	dpids := make(map[int]int)
	for rows.Next() {
		var dpid int
		err = rows.Scan(&dpid)
		if err != nil {
			return err
		}
		dpids[dpid] = 1
	}
	return c.JSON(http.StatusOK, dpids)

}

// 获取经营单位操作状态
func AddJydwCzztRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	jd := c.FormValue("JIDU")
	var sjmDate string
	if jd == "1" {
		sjmDate = nf + "/03/31"
	} else if jd == "2" {
		sjmDate = nf + "/06/30"
	} else if jd == "3" {
		sjmDate = nf + "/09/30"
	} else {
		sjmDate = nf + "/12/31"
	}
	rows, err := base.MYDB.Raw(`select distinct "ZBID" FROM (select "ZBID","DFJD" from "SH_JHCW_ZBDF"
	UNION ALL select distinct "ZBID","DFJD" from "SH_JHCW_ZBZL") T where "DFJD" = ? `, sjmDate).Rows()
	if err != nil {
		return err
	}
	dpids := make(map[int]int)
	for rows.Next() {
		var dpid int
		err = rows.Scan(&dpid)
		if err != nil {
			return err
		}
		dpids[dpid] = 1
	}
	return c.JSON(http.StatusOK, dpids)

}

// 设置负利润清零
func AddCompFlrqlRouter(c echo.Context) error {
	acidStr := c.FormValue("ACID")
	acid, err := strconv.Atoi(acidStr)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数acid不能为空，并且必须为整数！"))
	}
	tx := base.MYDB.Begin()
	var count int
	rows := base.MYDB.Raw(`SELECT count(*)  from "SH_INDI_FLRQL" WHERE "ACID"=?`, acid).Row()
	err = rows.Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		err = tx.Exec(`update "SH_INDI_FLRQL" set "ISFLRQL"=1  where "ACID"=?`, acid).Error
		if err != nil {
			return err
		}
	} else {
		err := tx.Exec(`INSERT INTO "SH_INDI_FLRQL" ("ACID", "ISFLRQL") VALUES (?, 1)`, acid).Error
		if err != nil {
			return err
		}
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.DICT_COMP_FLRQL")
	return c.JSON(http.StatusOK, "OK")
}

// 取消负利润清零
func DeleteCompFlrqlRouter(c echo.Context) error {
	acidStr := c.FormValue("ACID")
	acid, err := strconv.Atoi(acidStr)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Errorf("参数acid不能为空，并且必须为整数！"))
	}
	tx := base.MYDB.Begin()
	var count int
	rows := base.MYDB.Raw(`SELECT count(*)  from "SH_INDI_FLRQL" WHERE "ACID"=?`, acid).Row()
	err = rows.Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		err = tx.Exec(`update "SH_INDI_FLRQL" set "ISFLRQL"=0  where "ACID"=?`, acid).Error
		if err != nil {
			return err
		}
	} else {
		err = tx.Exec(`INSERT INTO "SH_INDI_FLRQL" ("ACID", "ISFLRQL") VALUES (?, 0)`, acid).Error
		if err != nil {
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	tx.Commit()
	ch := base.CHDB.Begin()
	ch.Exec("system reload dictionary pas_olap_nj.DICT_COMP_FLRQL")
	return c.JSON(http.StatusOK, "OK")
}

// 获取风险管理操作人
func SaveFxglCzrRouter(c echo.Context) (err error) {
	zhtczr := c.FormValue("CZR")
	bm := c.FormValue("BMID")
	err = base.MYDB.Exec(`update "SH_JHCW_FXGL_PERM" set "CZR"=?  where  "BMID" = ? `, zhtczr, bm).Error
	if err != nil {
		return err
	}
	var emid int
	emid, err = strconv.Atoi(zhtczr)
	if err != nil {
		return err
	}
	err = popedom.GrantPermissionToUser(emid, 230912, "K")
	return
}

// 获取风险管理操作状态
func AddFxglCzztRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	jd := c.FormValue("JIDU")
	var sjmDate string
	if jd == "1" {
		sjmDate = nf + "/03/31"
	} else if jd == "2" {
		sjmDate = nf + "/06/30"
	} else if jd == "3" {
		sjmDate = nf + "/09/30"
	} else {
		sjmDate = nf + "/12/31"
	}
	rows, err := base.MYDB.Raw(`select  distinct "DPID" from "SH_JHCW_FXGL" where "DFJD"=? `, sjmDate).Rows()
	if err != nil {
		return err
	}
	dpids := make(map[int]int)
	for rows.Next() {
		var dpid int
		err = rows.Scan(&dpid)
		if err != nil {
			return err
		}
		dpids[dpid] = 1
	}
	return c.JSON(http.StatusOK, dpids)

}

/**保存类别绩效绩效标准修改*/
func AddLbjxJxbzRouter(c echo.Context) error {
	var datas map[int]float64
	nf := c.FormValue("NIAN")
	jsonStr := c.FormValue("valueMap")
	err := json.Unmarshal([]byte(jsonStr), &datas)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	for key, value := range datas {
		err = base.MYDB.Exec(`UPDATE "SH_JHCW_LBJX_LBXS" SET "JXBZ"=? WHERE "TPID"=? and "NF" = ?`, value, key, nf).Error
		if err != nil {
			return err
		}
	}

	return c.JSON(http.StatusOK, "OK")
}

/**插入类别绩效除当年其余年的信息*/
func InsertLbjxRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	err := base.MYDB.Exec(`INSERT INTO "SH_JHCW_LBJX_LBXS" ("NF", "TPNM","TPID","JXBZ")
	values
	(?,'经营单位班子(正职)',11,0),
	(?,'经营单位班子(副职)',12,0),
	(?,'经营单位班子(助理)',13,0),
	(?,'分行管理部室中层(正职)',21,0),
	(?,'分行管理部室中层(副职)',22,0),
	(?,'分行管理部室中层(助理)',23,0),
	(?,'分行管理部室员工、支行中后台员工及保护期市场人员',30,0),
    (?,'客户经理及经营单位室经理(一级)',41,0),
    (?,'客户经理及经营单位室经理(二级)',42,0),
    (?,'客户经理及经营单位室经理(三级)',43,0),
    (?,'客户经理及经营单位室经理(四级)',44,0)`, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf).Error
	if err != nil {
		return err
	}
	return c.JSON(http.StatusOK, "OK")
}

/**保存级别系数绩效标准修改*/
func AddLbjxJbxsRouter(c echo.Context) error {
	var datas map[string]float64
	nf := c.FormValue("NIAN")
	jsonStr := c.FormValue("valueMap")
	err := json.Unmarshal([]byte(jsonStr), &datas)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	for key, value := range datas {
		err = base.MYDB.Exec(`UPDATE "SH_JHCW_LBJX_JBXS" SET "JJXS"=? WHERE "JBMC"=? and "NF" = ?`, value, key, nf).Error
		if err != nil {
			return err
		}
	}
	return c.JSON(http.StatusOK, "OK")
}

/**插入级别系数除当年其余年的信息*/
func InsertJbxsRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	err := base.MYDB.Exec(`INSERT INTO "SH_JHCW_LBJX_JBXS" ("NF", "JBMC", "JJXS")
	values
	(?,'室经理',0),
	(?,'室副经理',0),
	(?,'一级优等',0),
	(?,'一级高等',0),
	(?,'一级一等',0),
	(?,'一级二等',0),
	(?,'一级三等',0),
	(?,'二级优等',0),
	(?,'二级高等',0),
	(?,'二级一等',0),
	(?,'二级二等',0),
	(?,'二级二等',0),
	(?,'三级优等',0),
	(?,'三级高等',0),
	(?,'三级一等',0),
	(?,'三级二等',0),
	(?,'三级三等',0),
	(?,'四级优等',0),
	(?,'四级高等',0),
	(?,'四级一等',0),
	(?,'四级二等',0),
	(?,'四级三等',0),
	(?,'见习',0)`, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf).Error
	if err != nil {
		return err
	}
	return c.JSON(http.StatusOK, "OK")
}

type JXFFZT struct {
	FFJD     int     `gorm:"column:FFJD"`     //发放季度
	DPID     int     `gorm:"column:DPID"`     //部门id
	PDID     int     `gorm:"column:PDID"`     //部门id
	EMNO     string  `gorm:"column:EMNO"`     //工号
	EMNM     string  `gorm:"column:EMNM"`     //姓名
	YWJXTZJE float64 `gorm:"column:YWJXTZJE"` //业务绩效调整金额
	YJJXTZJE float64 `gorm:"column:YJJXTZJE"` //业绩绩效调整金额
	TZYY     string  `gorm:"column:TZYY"`     //调整原因
}

// TableName
func (JXFFZT) TableName() string {
	return "SH_JHCW_JXFF_YGJX_TZJE"
}

// 设置调整金额
func AddTzjeRouter(c echo.Context) error {
	var datas []JXFFZT
	jsonStr := c.FormValue("valueMap")
	err := json.Unmarshal([]byte(jsonStr), &datas)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	ffjd := datas[0].FFJD
	pdid := datas[0].PDID
	ffjd_str := strconv.Itoa(ffjd)
	nian, _ := strconv.Atoi(ffjd_str[0:4])
	month := ffjd_str[4:]
	var sjid int
	var jidu int
	if month == "01" {
		sjid = 1
		jidu = (nian-1)*10 + 4
	} else if month == "02" {
		sjid = 2
		jidu = (nian-1)*10 + 4
	} else if month == "03" {
		sjid = 3
		jidu = (nian-1)*10 + 4
	} else if month == "1" {
		sjid = 4
		jidu = (nian-1)*10 + 4
	} else if month == "04" {
		sjid = 5
		jidu = nian*10 + 1
	} else if month == "05" {
		sjid = 6
		jidu = nian*10 + 1
	} else if month == "06" {
		sjid = 7
		jidu = nian*10 + 1
	} else if month == "2" {
		sjid = 8
		jidu = nian*10 + 1
	} else if month == "07" {
		sjid = 19
		jidu = nian*10 + 2
	} else if month == "08" {
		sjid = 10
		jidu = nian*10 + 2
	} else if month == "09" {
		sjid = 11
		jidu = nian*10 + 2
	} else if month == "3" {
		sjid = 12
		jidu = nian*10 + 2
	} else if month == "10" {
		sjid = 13
		jidu = nian*10 + 3
	} else if month == "11" {
		sjid = 14
		jidu = nian*10 + 3
	} else if month == "12" {
		sjid = 15
		jidu = nian*10 + 3
	} else if month == "4" {
		sjid = 16
		jidu = nian*10 + 3
	}
	tx := base.MYDB.Begin()
	var state int
	tx.Raw(`select "CZ" from "SH_JHCW_JXSD_PERM" where "NIAN" = ? AND "SJ"=?`, nian, sjid).Row().Scan(&state)
	if state != 1 {
		tx.Callback()
		err = fmt.Errorf("年份%d月份%s没有开放调整！", nian, month)
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" set "TZYY"='', "YJJXTZJE"=0 , "YWJXTZJE"=0,"TZJECW"='' WHERE "FFJD"=? AND "PDID"=? `, ffjd, pdid).Error
	if err != nil {
		tx.Callback()
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	err = tx.Exec(`DELETE FROM "SH_JHCW_JXFF_YGJX_TZJE" WHERE "FFJD"=? AND "PDID"=? `, ffjd, pdid).Error
	if err != nil {
		tx.Callback()
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	for _, data := range datas {
		if data.YJJXTZJE == 0 && data.YWJXTZJE == 0 && data.TZYY == "" {
			continue
		}
		err = tx.Create(data).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	if base.AppConf.Database.Mysql != "" {
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" A,(
		 select EMNO,pdid from (
		  select c.EMNO EMNO,c.YJJX2 YJJX,c.YJJXTZJE YJJXTZJE,c.pdid pdid
		  from(
		   select a."EMNO" EMNO,a."YJJX" yjjx ,a."YJJX"*-0.2-1 YJJX2,a."PDID" pdid,
				  COALESCE(b."YJJXTZJE", 0.0) AS YJJXTZJE
		   from "SH_JHCW_JXFF_YGJX" a inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO" and a."FFJD" = b."FFJD"
		   where a."FFJD" =? and a."EMNM" not like '%公共%' and a."EMNO" not like 'shc%' and a."PDID"=?
		  ) c
		 ) d where YJJX>YJJXTZJE) B
		 set "TZJECW" = '员工业绩绩效调整不能低于20%'
		 where A."EMNO" = B.EMNO and A."PDID" = B.pdid and A."FFJD" =? and A."PDID" =?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" A,(
			select EMNO,pdid from (
			 select c.EMNO EMNO,c.YWJX2 YWJX,c.YWJXTZJE YWJXTZJE,c.pdid pdid
			 from(
			  select a."EMNO" EMNO,a."YWJX" ywjx,case when a."YWJX"<0 then 0 else a."YWJX" end/3*-0.2-1 YWJX2,a."PDID" pdid,
					 COALESCE(b."YWJXTZJE", 0.0) AS YWJXTZJE
			  from "SH_JHCW_JXFF_YGJX" a inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO"
			  where a."FFJD" =? and a."EMNM" not like '%公共%' and a."EMNO" not like 'shc%' and a."PDID"=? and b."FFJD"=?
			 ) c
			) d where YWJX>YWJXTZJE) B
			set "TZJECW" = '员工业务绩效调整不能低于20%'
			where A."EMNO" = B.EMNO and A."PDID" = B.pdid and A."FFJD" =? and A."PDID" =?`, jidu, pdid, ffjd, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" a,(
			 select emno
			 from(
				  select a."EMNO" EMNO,COALESCE(b."YJJXTZJE", 0.0) AS YJJXTZJE,COALESCE(b."YWJXTZJE", 0.0) YWJXTZJE
				  from "SH_JHCW_JXFF_YGJX" a
				  inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO" and a."FFJD" = b."FFJD"
				  where a."EMZW" not like '%营业室%' AND (a."EMZW" like '%主持%' or a."EMZW" like '%负责人%' or a."EMZW" like '%正职%' ) and a."FFJD"=? and a."PDID"=? and a."EMZW"<>'社区银行负责人'
			 ) a
			 where YJJXTZJE >0 or YWJXTZJE>0
		) d
		set "TZJECW"  = '一把手不能增加绩效'
		where a."EMNO"=d.emno and a."FFJD" = ? AND a."PDID" = ?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" A,(
							select a."EMNO" EMNO
							from
								(select "EMNO" ,"PDID","FFJD"
								from "SH_JHCW_JXFF_YGJX"
								where "ISYYS" = 1)a
								inner join (
								select "EMNO" ,"PDID",coalesce("YJJXTZJE",0.0) YJJXTZJE,coalesce("YWJXTZJE",0.0) YWJXTZJE,"FFJD"
								from "SH_JHCW_JXFF_YGJX_TZJE") b on a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."FFJD" =b."FFJD"
								where YJJXTZJE < 0 or YWJXTZJE < 0 and a."FFJD"=? and a."PDID"=?) B
						set "TZJECW" = '营业室人员只能增不能减'
						where  A."EMNO" = B.EMNO and A."FFJD" =? and A."PDID" =?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" a,(
							select emno
							from(
								 select a."EMNO" EMNO,COALESCE(b."YJJXTZJE", 0.0) AS YJJXTZJE,COALESCE(a."YJJX", 0.0) YJJX
								 from "SH_JHCW_JXFF_YGJX" a
								 inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO" and a."FFJD" = b."FFJD"
								 where a.FFJD = ? and a.PDID =  ?
							) a
							where YJJXTZJE != 0 AND  YJJX = 0
						) d
						set "TZJECW"  = '该员工不涉及业绩绩效'
						where a."EMNO"=d.emno and a."FFJD" = ? AND a."PDID" = ?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" A,(
							select EMNO,pdid from (
							 select c.EMNO EMNO,c.ywjx YWJX,c.YWJXTZJE YWJXTZJE,c.pdid pdid
							 from(
							  select a."EMNO" EMNO,a."YWJX" ywjx,a."PDID" pdid,
									 COALESCE(b."YWJXTZJE", 0.0) AS YWJXTZJE
							  from "SH_JHCW_JXFF_YGJX" a inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO" and a."FFJD" = b."FFJD"
							  where a."FFJD" = ? and a."PDID"= ? 
							 ) c
							) d where YWJX = 0 and YWJXTZJE < 0) B
							set "TZJECW" = '该员工没有业务绩效不能减少业务绩效'
						where A."EMNO" = B.EMNO and A."PDID" = B.pdid and A."FFJD" = ? and A."PDID" = ?`, jidu, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	} else {
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" A set "TZJECW" = '员工业绩调整不能低于20%'
		from (
		 select EMNO,pdid from (
		  select  c.EMNO EMNO,c.YJJX2 YJJX,c.YWJX2 YWJX,c.YJJXTZJE YJJXTZJE ,c.YWJXTZJE YWJXTZJE,c.pdid pdid
		  from(
		   select a."EMNO" EMNO,a."YJJX" yjjx ,a."YWJX" YWJX,a."YJJX"*-0.2 YJJX2,"YWJX"*-0.2 YWJX2,a."PDID" pdid,
				  COALESCE(b."YJJXTZJE", 0.0) AS YJJXTZJE,COALESCE(b."YWJXTZJE", 0.0) AS YWJXTZJE
		   from "SH_JHCW_JXFF_YGJX" a inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO" and a."FFJD" = b."FFJD"
		   where a."FFJD" =? and a."EMNM" not like '%公共%' and a."PDID"=?
		  ) c
		 ) d where YJJX>YJJXTZJE or YWJX>YWJXTZJE) B where A."EMNO" = B.EMNO  and A."PDID" = B.pdid and A."FFJD" =? and A."PDID" =?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" a set "TZJECW"  = '一把手不能增加绩效'
		from (
			 select emno
			 from(
				  select a."EMNO" EMNO,COALESCE(b."YJJXTZJE", 0.0) AS YJJXTZJE,COALESCE(b."YWJXTZJE", 0.0) YWJXTZJE
				  from "SH_JHCW_JXFF_YGJX" a
				  inner join "SH_JHCW_JXFF_YGJX_TZJE" b on a."EMNO" = b."EMNO" and a."FFJD" = b."FFJD"
				  where a."FFJD"=? and a."PDID"=? and (a."EMZW" like '%主持%' or a."EMZW" like '%负责人%' and a."EMZW"<>'社区银行负责人')
			 ) a
			 where YJJXTZJE >0 or YWJXTZJE>0
		) d
		where a."EMNO" =d.emno and a."FFJD" = ? AND a."PDID" = ?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX" A set "TZJECW" = '营业室人员只能增不能减'
						from (
							select a."EMNO" EMNO
							from
								(select "EMNO" ,"PDID","FFJD"
								from "SH_JHCW_JXFF_YGJX"
								where "ISYYS" = 1)a
								inner join (
								select "EMNO" ,"PDID",coalesce("YJJXTZJE",0.0) YJJXTZJE,coalesce("YWJXTZJE",0.0) YWJXTZJE,"FFJD"
								from "SH_JHCW_JXFF_YGJX_TZJE") b on a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."FFJD" =b."FFJD"
								where YJJXTZJE < 0 or YWJXTZJE < 0 and a."FFJD"=? and a."PDID"=?) B
						where  A."EMNO" = B.EMNO and A."FFJD" =? and A."PDID" =?`, ffjd, pdid, ffjd, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	err = tx.Commit().Error
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	var errCount int
	var YSYJJXTZJE, YSYWJXTZJE float64
	base.MYDB.Raw(`select abs(sum("YJJXTZJE")) YSYJJXTZJE,abs(sum("YWJXTZJE")) YSYWJXTZJE
	from "SH_JHCW_JXFF_YGJX_TZJE"
	where "FFJD" = ? and "PDID"=? `, ffjd, pdid).Row().Scan(&YSYJJXTZJE, &YSYWJXTZJE)
	base.MYDB.Raw(`select count(*)
	from "SH_JHCW_JXFF_YGJX"
	where "FFJD" = ? and "TZJECW" is not null and "TZJECW" != '' and "PDID" =?`, ffjd, pdid).Row().Scan(&errCount)
	if YSYJJXTZJE > 0.01 || YSYWJXTZJE > 0.01 {
		var yjjx, ywjx, xghyjjx, xghywjx float64
		base.MYDB.Raw(`select sum(YJJX) YJJX,sum(YJJX2) YJJX2
		from (
			select sum("YJJX") YJJX,sum("YJJX") YJJX2
			from "SH_JHCW_JXFF_YGJX"
			where "FFJD" = ? and "PDID"=?
			union all
			select 0,sum("YJJXTZJE") YSYJJXTZJE
			from "SH_JHCW_JXFF_YGJX_TZJE"
			where "FFJD" = ? and "PDID"=?
		) T `, ffjd, pdid, ffjd, pdid).Row().Scan(&yjjx, &xghyjjx)
		base.MYDB.Raw(`select sum(YWJX) YWJX,sum(YWJX2) YWJX2
		from (
			select sum("YWJX")/3 YWJX,sum("YWJX")/3 YWJX2
			from "SH_JHCW_JXFF_YGJX"
			where "FFJD" = ? and "PDID"=?
			union all
			select 0,sum("YWJXTZJE") YSYWJXTZJE
			from "SH_JHCW_JXFF_YGJX_TZJE"
			where "FFJD" = ? and "PDID"=?
		) T `, jidu, pdid, ffjd, pdid).Row().Scan(&ywjx, &xghywjx)
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Sprintf("调整金额与原金额不一致,原业绩绩效为：%.2f元,修改后的业绩绩效为：%.2f元,原业务绩效为：%.2f元,修改后的业务绩效为:%.2f元。", yjjx, xghyjjx, ywjx, xghywjx))
	} else if errCount > 0 {
		return echo.NewHTTPError(http.StatusBadRequest, "调整金额不合规,请查看错误提示")
	} else {
		if base.AppConf.Database.Mysql != "" {
			err = base.MYDB.Exec(`UPDATE "SH_JHCW_JXFF_YGJX" b,"SH_JHCW_JXFF_YGJX_TZJE" a
			SET  b."TZYY"=a."TZYY",b."YJJXTZJE"=a."YJJXTZJE",b."YWJXTZJE"=a."YWJXTZJE",b."TZJECW"=''
			where a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."FFJD" = b."FFJD" and a."FFJD" =? and a."PDID" =?`, ffjd, pdid).Error
			if err != nil {
				return echo.NewHTTPError(http.StatusBadRequest, err.Error())
			}
		} else {
			err = base.MYDB.Exec(`UPDATE "SH_JHCW_JXFF_YGJX" b
			SET  "TZYY"=a."TZYY" , "YJJXTZJE"=a."YJJXTZJE" , "YWJXTZJE"=a."YWJXTZJE" ,"TZJECW"=''
			from "SH_JHCW_JXFF_YGJX_TZJE" a
			where a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."FFJD" = b."FFJD" and a."FFJD" =? and a."PDID" =?`, ffjd, pdid).Error
			if err != nil {
				return echo.NewHTTPError(http.StatusBadRequest, err.Error())
			}
		}
	}
	user := c.Get("user").(*jwt.Token)
	claims := user.Claims.(*UserLoginClaims)
	base.LogToDB(claims.UserID, 20230919, fmt.Sprintf("调整%d季部门%d的绩效", ffjd, pdid), c.RealIP())
	return c.JSON(http.StatusOK, "OK")
}

// 设置绩效锁定状态
func SetJxsdczRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	czStr := c.FormValue("CZ")
	sj := c.FormValue("ID")
	cz, err := strconv.ParseInt(czStr, 10, 8)
	if err != nil {
		return err
	}
	var count uint8
	err = base.MYDB.Raw(`select count(*) from "SH_JHCW_JXSD_PERM" where "NIAN"= ? AND "SJ" = ?`, nf, sj).Row().Scan(&count)
	if err != nil {
		return err
	}
	var sqlStr string
	if count > 0 {
		sqlStr = `update "SH_JHCW_JXSD_PERM" set "CZ"=? where "NIAN"= ? AND "SJ" = ? `
	} else {
		sqlStr = `insert into "SH_JHCW_JXSD_PERM"("CZ","NIAN","SJ") values(?,?,?)`
	}
	err = base.MYDB.Exec(sqlStr, cz, nf, sj).Error
	if err != nil {
		return err
	}
	return c.JSON(http.StatusOK, "OK")
}

/**插入绩效锁定除当年其余年的信息*/
func InserJxsdztRouter(c echo.Context) error {
	nf := c.FormValue("NIAN")
	err := base.MYDB.Exec(`INSERT INTO "SH_JHCW_JXSD_PERM"("NIAN", "SJ")
	VALUES(?, 1),
	(?, 2),
	(?,3),
	(?, 4),
	(?, 5),
	(?, 6),
	(?, 7),
	(?, 8),
	(?, 9),
	(?, 10),
	(?, 11),
	(?, 12),
	(?, 13),
	(?, 14),
	(?, 15),
	(?, 16),
	(?, 17)`, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf, nf).Error
	if err != nil {
		return err
	}
	return c.JSON(http.StatusOK, "OK")
}

type NZJXJXFFTZ struct {
	NF       int     `gorm:"column:NF"`       //发放季度
	DPID     int     `gorm:"column:DPID"`     //部门id
	PDID     int     `gorm:"column:PDID"`     //部门id
	EMNO     string  `gorm:"column:EMNO"`     //工号
	EMNM     string  `gorm:"column:EMNM"`     //姓名
	NZJXTZJE float64 `gorm:"column:NZJXTZJE"` //年终业绩绩效调整金额
	TZYY     string  `gorm:"column:TZYY"`
}

// TableName
func (NZJXJXFFTZ) TableName() string {
	return "SH_JHCW_JXFF_YGJX_NZ_TZJE"
}

// 设置年终业绩绩效调整金额
func AddNzTzjeRouter(c echo.Context) error {
	var datas []NZJXJXFFTZ
	jsonStr := c.FormValue("valueMap")
	err := json.Unmarshal([]byte(jsonStr), &datas)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	tx := base.MYDB.Begin()
	var state int
	nf := datas[0].NF
	tx.Raw(`select "CZ" from "SH_JHCW_JXSD_PERM" where "NIAN" = ? AND "SJ"= 17`, nf).Row().Scan(&state)
	if state != 1 {
		tx.Callback()
		err = fmt.Errorf("年份%d年终业绩绩效没有开放调整！", nf)
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	pdid := datas[0].PDID
	err = tx.Exec(`UPDATE "SH_JHCW_JXFF_YGJX_NZ" SET "NZJXTZJE"=0 , "TZYY" = '' ,"TZJECW" = '' WHERE "NF" = ? and "PDID"  = ?`, nf, pdid).Error
	if err != nil {
		tx.Callback()
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	err = tx.Exec(`DELETE FROM "SH_JHCW_JXFF_YGJX_NZ_TZJE" WHERE "NF"=? AND "PDID"=? `, nf, pdid).Error
	if err != nil {
		tx.Callback()
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	for _, data := range datas {
		if data.NZJXTZJE == 0 && data.TZYY == "" {
			continue
		}
		err = tx.Create(data).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	if base.AppConf.Database.Mysql != "" {
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX_NZ" A,(
			select EMNO,pdid from (
			 select c.EMNO EMNO,c.YWJX2 YWJX,c.NZJXTZJE YWJXTZJE,c.pdid pdid
			 from(
			  select a."EMNO" EMNO,case when a."NZJX"<0 then 0 else a."NZJX" end/3*-0.2-1 YWJX2,a."PDID" pdid,
					 COALESCE(b."NZJXTZJE", 0.0) AS NZJXTZJE
			  from (SELECT "EMNO" ,"EMNM" ,"PDID" ,"NF" ,ROUND(SUM(YJJX/7*3),2)"NZJX"  
					FROM "SH_JHCW_JXFF_YGJX_NZ"  
					GROUP BY "EMNO" ,"EMNM" ,"PDID" ,"NF" ) a inner join "SH_JHCW_JXFF_YGJX_NZ_TZJE" b on a."EMNO" = b."EMNO"
			  where a."NF" = ? and a."EMNM" not like '%公共%' and a."EMNO" not like 'shc%' and a."PDID" = ?  and b."NF" = ?
			 ) c
			) d where YWJX>YWJXTZJE) B
			set "TZJECW" = '员工年终业绩绩效调整不能低于20%'
			where A."EMNO" = B.EMNO and A."PDID" = B.pdid and A."nf" =? and A."PDID" = ? `, nf, pdid, nf, nf, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX_NZ" a,(
			select emno
			from(
				 select a."EMNO" EMNO,COALESCE(b."NZJXTZJE", 0.0) AS NZJXTZJE,a."PDID"  
				 from "SH_JHCW_JXFF_YGJX_NZ" a
				 inner join "SH_JHCW_JXFF_YGJX_NZ_TZJE" b on a."EMNO" = b."EMNO" and a."NF" = b."NF"
				 where a."EMZW" not like '%营业室%' AND (a."EMZW" like '%主持%' or a."EMZW" like '%负责人%' or a."EMZW" like '%正职%') and a."NF" = ? and a."PDID" = ?
			) a
			where NZJXTZJE >0 
	   ) d
	   set "TZJECW"  = '一把手不能增加绩效'
	   where a."EMNO"=d.emno and a."NF" = ? AND a."PDID" = ?`, nf, pdid, nf, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX_NZ" A,(
							select a."EMNO" EMNO
							from
								(select "EMNO" ,"PDID","NF" 
								from "SH_JHCW_JXFF_YGJX_NZ"
								where "ISYYS" = 1)a
								inner join (
								select "EMNO" ,"PDID",coalesce("NZJXTZJE",0.0) NZJXTZJE,"NF"
								from "SH_JHCW_JXFF_YGJX_NZ_TZJE") b on a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."NF" =b."NF"
								where NZJXTZJE < 0  and a."NF" = ? and a."PDID" = ?) B
						set "TZJECW" = '营业室人员只能增不能减'
						where  A."EMNO" = B.EMNO and A."NF" = ? and A."PDID" = ?`, nf, pdid, nf, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	} else {
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX_NZ" A set "TZJECW" = '员工业绩调整不能低于20%'
		from (
		 select EMNO,pdid from (
		  select  c.EMNO EMNO,c.NZJX1 NZJX1,c.NZJX2 NZJX2,c.NZJXTZJE NZJXTZJE,c.pdid pdid
		  from(
		   select a."EMNO" EMNO,a."EMNM" EMNM,a."PDID" pdid,a.NZJX NZJX1,a.NZJX*-0.2 NZJX2,b."NZJXTZJE" NZJXTZJE
		   from (select "EMNO" ,"EMNM" , round(sum("YJJX"/7*3),2) NZJX ,"PDID" ,"NF"
				from "SH_JHCW_JXFF_YGJX_NZ"		
				group by "EMNO" ,"EMNM" ,"PDID","NF" ) a 
		   inner join "SH_JHCW_JXFF_YGJX_NZ_TZJE" b on a."EMNO" = b."EMNO" and a."NF" = b."NF"
		   where a."NF" = ? and a."EMNM" not like '%公共%' and a."PDID" = ?	
		  ) c
		 ) d where NZJX2 > NZJXTZJE  ) B where A."EMNO" = B.EMNO  and A."PDID" = B.pdid and A."NF" = ? and A."PDID" = ?`, nf, pdid, nf, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX_NZ" A set "TZJECW"  = '一把手不能增加绩效'
		from (
			 select emno
			 from(
				  select a."EMNO" EMNO,COALESCE(b."NZJXTZJE", 0.0) AS NZJXTZJE
				  from "SH_JHCW_JXFF_YGJX_NZ" a
				  inner join "SH_JHCW_JXFF_YGJX_NZ_TZJE" b on a."EMNO" = b."EMNO" and a."NF" = b."NF"
				  where a."NF" = ? and a."PDID" = ? and (a."EMZW" like '%主持%' or a."EMZW" like '%负责人%' and a."EMZW"<>'社区银行负责人')
			 ) a
			 where NZJXTZJE >0 
		) d
		where A."EMNO" =d.emno and a."NF" = ? AND a."PDID" = ?	`, nf, pdid, nf, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
		err = tx.Exec(`update "SH_JHCW_JXFF_YGJX_NZ" A set "TZJECW" = '营业室人员只能增不能减'
		from (
			select a."EMNO" EMNO
			from
				(select "EMNO" ,"PDID","NF"
				from "SH_JHCW_JXFF_YGJX_NZ"
				where "ISYYS" = 1)a
				inner join (
				select "EMNO" ,"PDID",coalesce("NZJXTZJE",0.0) NZJXTZJE,"NF"
				from "SH_JHCW_JXFF_YGJX_NZ_TZJE") b on a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."NF" =b."NF"
				where NZJXTZJE < 0  and a."NF"=? and a."PDID"=?) B
		where  A."EMNO" = B.EMNO and A."NF" =? and A."PDID" =?`, nf, pdid, nf, pdid).Error
		if err != nil {
			tx.Callback()
			return echo.NewHTTPError(http.StatusBadRequest, err.Error())
		}
	}
	err = tx.Commit().Error
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}
	var errCount int
	var YSYJJXTZJE float64
	base.MYDB.Raw(`select abs(sum("NZJXTZJE")) NZJXTZJE
	from "SH_JHCW_JXFF_YGJX_NZ_TZJE"
	where "NF" = ? and "PDID"=? `, nf, pdid).Row().Scan(&YSYJJXTZJE)
	base.MYDB.Raw(`select count(*)
	from "SH_JHCW_JXFF_YGJX_NZ"
	where "NF" = ? and "TZJECW" is not null and "TZJECW" != '' and "PDID" =?`, nf, pdid).Row().Scan(&errCount)
	if YSYJJXTZJE > 0.01 {
		var yjjx, xghyjjx float64
		base.MYDB.Raw(`select sum(NZJX) NZJX,sum(NZJX2) NZJX2
		from (
			select sum("NZJX") NZJX,sum("NZJX") NZJX2
			from "SH_JHCW_JXFF_YGJX_NZ"
			where "NF" = ? and "PDID" = ?
			union all
			select 0,sum("NZJXTZJE") NZJXTZJE
			from "SH_JHCW_JXFF_YGJX_NZ_TZJE"
			where "NF" = ? and "PDID" = ?
		) T `, nf, pdid, nf, pdid).Row().Scan(&yjjx, &xghyjjx)
		return echo.NewHTTPError(http.StatusBadRequest, fmt.Sprintf("调整金额与原金额不一致,原年终业绩绩效为：%.2f元,修改后的年终业绩绩效为：%.2f元.", yjjx, xghyjjx))
	} else if errCount > 0 {
		return echo.NewHTTPError(http.StatusBadRequest, "调整金额不合规,请查看错误提示")
	} else {
		if base.AppConf.Database.Mysql != "" {
			err = base.MYDB.Exec(`UPDATE "SH_JHCW_JXFF_YGJX_NZ" b,"SH_JHCW_JXFF_YGJX_NZ_TZJE" a
			SET  b."TZYY"=a."TZYY",b."NZJXTZJE"=a."NZJXTZJE",b."TZJECW"=''
			where a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."NF" = b."NF" and a."NF" =? and a."PDID" =?`, nf, pdid).Error
			if err != nil {
				return echo.NewHTTPError(http.StatusBadRequest, err.Error())
			}
		} else {
			err = base.MYDB.Exec(`UPDATE "SH_JHCW_JXFF_YGJX_NZ" b
			SET  "TZYY"=a."TZYY" , "NZJXTZJE"=a."NZJXTZJE","TZJECW"=''
			from "SH_JHCW_JXFF_YGJX_NZ_TZJE" a
			where a."EMNO" = b."EMNO" and a."PDID" = b."PDID" and a."NF" = b."NF" and a."NF" =? and a."PDID" =?`, nf, pdid).Error
			if err != nil {
				return echo.NewHTTPError(http.StatusBadRequest, err.Error())
			}
		}
	}
	user := c.Get("user").(*jwt.Token)
	claims := user.Claims.(*UserLoginClaims)
	base.LogToDB(claims.UserID, 20230919, fmt.Sprintf("调整%d季部门%d的绩效", nf, pdid), c.RealIP())
	return c.JSON(http.StatusOK, "OK")
}
